Measuring Exposure to the Credit Shock

ZIP Codes – County Name – Village Name – County Codes Matching Table

We use data from the Postleitzahlen Deutschland, which provides an accurate matching table between ZIP codes and KKZ codes, the county identifiers that are also used in the German Socio-Economic Panel. From these data, we obtain for each ZIP code plz the corresponding county code ags, the Landkreis name landkreis (if it is a landkreis), the Bundesland name and the location name ort. We keep KKZ at county level (five digits).

# load from website the .csv file with the matches
kreise_codes <- read.csv(
    url(glue::glue(
        "https://www.suche-postleitzahl.org/",
        "download_files/public/zuordnung_plz_ort_landkreis.csv"))
    ) %>%
    as.data.table()
# keep KKZ codes at county level in five digits
kreise_codes[, kkz := str_sub(
    fifelse(str_length(ags) == 7, paste0("0", ags), as.character(ags)), 1, 5)]
# in the .do file we put Landkreis and Kreis at the end of the string
# we do it here as well and then let's see
kreise_codes[, names := fifelse(
    str_trim(landkreis) == "", paste0(ort, " Stadt"),
    str_replace(landkreis,"(Landkreis|Kreis|Eifelkreis)\\s(.+)", "\\2 \\1"))]

First Step: ZIP Code Matching

First, we try to match all companies with their county code using the ZIP codes indicated in the Amadeus database.

# matching table zip-kkz keeping unique values in the previous data
match_zips <- unique(kreise_codes[, .(plz, kkz)])
# we keep zip code as integer
companies[, zipcode := as.integer(zipcode)]
# double check that Landkreis and Kreis are at the end of the 
# A[B, on = 'a', bb := i.b], join A with B and upddate A by reference
companies[match_zips, on = .(zipcode = plz), kkz := i.kkz]

Second Step: String Fuzzy Matching with County Names

After matching ZIP codes with the information in the Amadeus’ companies, we are left with 26994 unmatched firms. For most of them (26269) we have information of the region where those firms are located. We can use UTF-8 original names for the match, contrary to Stata that messes up all the time with strings. After some adjustments of the string names, we run a Jaro-Winkler distance algorithm to fuzzy matching the region name of firms without a match with the ZIP code (or missing ZIP code) with the region names in the matching table, specified in Landkreis for most of all the rural areas and Stadt for most of all the urban areas. We select for each unmatched companies’ region name the county name with the smallest distance in the matching table. The Jaro-Winkler distance is not perfect, so we need to perform some manual changes to fit the names after the fuzzy matching procedure.

# reverse epithets of counties just in case
companies[, region_nat := str_replace(
  region_nat,"(Landkreis|Kreis|Eifelkreis)\\s(.+)", "\\2 \\1")]
# select from the matching table only names and county codes
match_names <- unique(kreise_codes[, .(kkz, names)])
# select only the region names for unmatched firms in the step before
unmatched_regions <- data.table(
    names = unique(companies[is.na(kkz) & !is.na(region_nat), region_nat]))
# create grid for matching each region name with names in the matching table
scores <- expand.grid(unmatched_regions[, names], match_names[, names])
# run Jaro-Winkler string distance function
scores$dist <- stringdist::stringdist(scores$Var1, scores$Var2, method = "jw")
colnames(scores) <- c("region_nat", "names", "dist")
# there are some region names that are Bundesland names and we remove it
states <- unique(kreise_codes[bundesland != "Berlin", bundesland])
# select the name with the smallest distance for each region name
match_names <- scores %>%
    dplyr::filter(!(region_nat %in% states)) %>%
    dplyr::group_by(region_nat) %>%
    dplyr::arrange(dist) %>%
    dplyr::slice(1) %>%
    dplyr::select(region_nat, names) %>%
    as.data.table()
# add county codes back from the matching table
match_names[kreise_codes, on = .(names), kkz := i.kkz]
# manual corrections
match_names[region_nat %like% "München  Landeshaupt", names := "München Stadt"]
match_names[region_nat %like% "Lauenburg", names := "Herzogtum Lauenburg Kreis"]
match_names[region_nat %like% "Neuss", names := "Rhein-Neuss Kreis"]
match_names[region_nat %like% "Wendel", names := "St. Wendel Landkreis"]
# add county codes to the unmatched companies
companies[
    match_names, on = .(region_nat), kkz := fifelse(is.na(kkz), i.kkz, kkz)]

Third Step: String Fuzzy Matching with “City” Names

After using strings with region names for the match of companies with their county code, we still have 1107 unmatched firms. Of those firms, 925 have information about the city where the firm is located. First, we search for perfect match of of companies’ city names that are unique at county level in the matching table for Germany as a whole. Second, we use the Jaro-Winkler distance again combining information on the city with the Bundesland in the matching table to assign county codes for the remaining cities.

# select the city of firms that are left unmatched
unmatched_cities <- unique(
  companies[is.na(kkz) & !is.na(city_nat), c("city_nat", "region_nat")])
# select cities by couinty codes and their Bundesland
match_cities <- unique(kreise_codes[, .(kkz, ort, bundesland)])
# count towns with the same name for an indicator
match_cities[, n_cities := .N, by = .(ort)][, city_nat := toupper(ort)]
# exact match cities in the unmatched firms, when cities has unique name
unmatched_cities[
    match_cities[n_cities == 1, .(kkz, city_nat)],
    on = .(city_nat), kkz := i.kkz]

# fuzzy matching for remaining cities using bundesland names
match_cities[, names := paste0(city_nat, " - ", toupper(bundesland))]
scores <- expand.grid(
  unique(unmatched_cities[is.na(kkz), city_nat]), match_cities[, names])
scores$dist <- stringdist::stringdist(scores$Var1, scores$Var2, method = "jw")
colnames(scores) <- c("city_nat", "names", "dist")
scores <- scores %>%
    dplyr::group_by(city_nat) %>%
    dplyr::arrange(dist) %>%
    dplyr::slice(1) %>%
    dplyr::select(city_nat, names) %>%
    as.data.table()
# manual correction of bad matching
nuisance_cities <- c("SENSBACHTAL", "RIEZLERN", "HIRSCHEGG", "TWIEFLINGEN",
                     "HOYERSHAUSEN", "ZÖBLITZ", "WIES", "NEUNKIRCHEN",
                     "HELSINKI", "SACHSEN", "KUOPIO", "TAMPERE")
scores <- scores[!(city_nat %in% nuisance_cities)]
# manual correction city mismatch
city_mismatch <- c("LICHTE", "HADMERSLEBEN", "BRANDENBURG", "MARL", "VOERDE",
                   "FREIBURG", "LUDWIGSHAFEN", "ST. WENDEL", "MUNICH")
city_correction <- c("Sonneberg - Thüringen", "Oschersleben - Sachsen-Anhalt",
                     "Brandenburg an der Havel - Brandenburg",
                     "Marl - Nordrhein-Westfalen",
                     "Voerde (Niederrhein) - Nordrhein-Westfalen",
                     "Freiburg im Breisgau - Baden-Württemberg",
                     "Ludwigshafen am Rhein - Rheinland-Pfalz",
                     "Sankt Wendel - Saarland", "München - Bayern")
for (s in 1:length(city_mismatch)) {
    scores[city_nat == city_mismatch[s], names := toupper(city_correction[s])]
}

scores <- merge(scores, match_cities[, .(names, kkz)], by = "names")
unmatched_cities[
  scores[, .(kkz, city_nat)], on = .(city_nat),
  kkz := fifelse(is.na(kkz), i.kkz, kkz)]
unmatched_cities <- unique(unmatched_cities[!is.na(kkz), .(kkz, city_nat)])
# add county codes to the unmatched companies
companies[unmatched_cities, on = .(city_nat),
          kkz := fifelse(is.na(kkz), i.kkz, kkz)]

Unfortunately, we have to manually correct some of the cities and, after merging back to the companies table, we have been able to match all but 227 firms, with a total of 1516136 firms in the sample.

Fourth-Step: Harmonize county codes with the official German Socio-Economic Panel Recoded County Codes.

The last operation to do is to match the county codes we have matched in the Amadeus sample with the county level codes contained in the German Socio-Economic Panel. In fact, the SOEP with county identifiers we access in the SOEPRemote has re-coded county codes keeping track of counties that have been merged at different points in time between 2000 and 2016, but never the inverse process. We have retrieved a table from the internal server at DIW Berlin that contains the matches between KKZ and KKZ_REK, namely the standard codes and the re-coded ones.

We convert the county codes from string to numeric (as some of them they keep a zero in the front, which is not the case for the SOEPRemote codes), and we perform the match using the integer codes.

Two-Digits Industry Sectors in the Firm Sample

From the companies profile, for each firm we have information of the four digits NACE v2 industry code. We group firms using the two-digit specification of industry sector to see the number of firms in the sample after restricting by incorporation date and presence of bank relationships for each group. There are 18 firms for which the industry code is not available. Industry composition within a county should be taken care by the county-level fixed effects in our identification.

A large part of the firms are in management consultancy activities, followed by construction and wholesale trade. On average, heavy industry firms have the most number of bank relationships, whereas the most number of bank relationships with Commerzbank are on wholesale trade, manifacture of machinery and equipment, warehousing, manufacture of electronic products and manufacture of rubber and plastic products. We also report the average industry share at county level. We consider industry shares at county level as frequency weight for the size of an industry within a county.

Exploring the Financial Panel and Firm Size Categories

In this section, we explore the financial variables we fetched from the financials_x tables in Amadeus. In primis, we remove duplicates for the entire financials data where there are both consolidated and unconsolidated data. We summarize the financial variables for the entire sample after filtering duplicates by firm size category, to make some sense of what it means to be a firm of size X in the Amadeus data, how complete are the financial information and which years are more prominent, given the existing survivorship bias.

From the summary statistics we do see the survivorship bias for all firms in the sample. The closing date year is 2014 at the 25th percentile, which does not give us much hope to have information at the years before the credit shock. The most complete information comes from the number of employees, current assets, current liabilities, total assets, long-term debt, total equity and right-hand side of the balance sheet and solvency ratio. We try to isolate those variables for the entire period available for those firms restricted with the incorporation date and the bank relationships data.

We compare the current firms financial information with the firms we were using in the previous sample in the draft, to see whether we can retrieve some financial data for those. The numbers are actually very similar.

For those firms and those variables, we would like to understand how many we have for the years before the credit shock, from 2000 to 2008 as reference years. From the table below, we see that information of number and cost of employees is very limited, whereas information on current assets, current liabilities and long-term debt is more available. For weighting using financials data, it makes sense to use alternatively the three years before the shock, which are 2006, 2007, and 2008, with more emphasis on 2007 as we have more information than 2006 and it is safe enough to not be affected by the credit shock than 2008, net of the delay in reporting of Amadeus.

Weights using the Financial Information

From the financial data we have, we take into account two dimensions of the firm within the industry clusters at county level:

• the sensitivity of a firm to liquidity constraints generated by a credit shock, which give us how the firm compared to the county-level peers would be more exposed to a short-term credit shock, and

• the county-level firm relevance, which gives us how important is the firm for the county’s economy compared to the county-level peers.

For the first measure, we consider the current ratio, which is current assets over current liabilities. It is a type of liquidity ratio that measures a company’s ability to pay off its current liabilities with its total current assets such as cash, accounts receivable and inventories. In case of a credit shock, the firms needs to rely on liquidity to pay off current liabilities, such as the salaries, and the higher the current ratio, the better is the company’s liquidity position.

For the second measure, we can either consider the number of employees or the cost of employees, which is also indicative of the size of the firm in a non-categorical fashion. From the summary table of financials for the subsample, we see that current assets and liabilities can be negative or zero, as well as cost of employees.

It is reasonable to think that the sensitivity of a firm depends on its size and industry. Firms within an industry might respond differently to credit shocks than industries in a different industry. County-level industry composition share of firm size should be absorbed by fixed effects in the identification, but we can still compare firms within the county by their current ratio.

We take the inverse rank of firms within a county by their current ratio and divide this rank by the sum of total values of ranks at county level, such that the weights sum up to one. Ranking takes care of the negative current assets and liabilities, as well as infinite values due to zero liabilities. Identical current ratios are weighted equally. The smaller the current ratio of a firm, the higher the rank and the bigger the weight assigned at county level.

We use the number or the cost of employees at firm level to weight for the size of a firm within a county. We combine the current ratio weights and the employees weights computing two different weighted averages for each weight and then divide by 2. The following table gives a summary of the weights with the financial information at firm level.

We perform the same operation on the sample of firms retrieved in the previous draft, to see whether we have similar weights on summaries, and we keep the option to use the previous data.

For the entire sample of firms, including those of which we do not have the financial information, we would like to consider the mean of the financial values of the firms in the same size category within their county for which we do have information, and assign it to all firms. However, it is possible that this information covers all firm size categories within a county, it can be that some of the firms in the sample with the bank relationships have no match. We pool together the all the years around 2006, from 2004 and 2008, and we take mean of the financial variables. We calculate the current ratio on the mean value, but we invert it considering 1/current ratio, such that the bigger the number obtained the more severe is the liquidity exposure.

There is a significant lower level of missing values for firm weights, but we are not able to cover the full sample in any case.

It would be nice to repeat the same operation for the sample of firms retrieved in the previous draft, but I have not saved the firm size label for the firms thus I am unable to assign the weights to quite some of the firms, dropping down to a sample of 549781 firms when using the financials data to retrieve the size category for the firms.

Exposure using Equal Weigthing

In the following histogram, we plot the distribution of the county-level Commerzbank dependence using the current firm sample with equal weights - just the arithmetic mean of firm-level Commerzbank dependence within a county - with the distributiuon of the county-level Commerzbank dependence using the previous sample we applied in the last draft. The mean of the distribution is smaller in the current sample (0.079 and 0.083), and we miss one county having less firms. Another important feature to notice for our design is that in the current firms’ sample we have one county with zero county-level Commerzbank dependence, Landau in der Pfalz (KKZ = 07313), and the missing county is Eisenach (KKZ = 16056). The county with zero Commerzbank dependence has two firms, with one bank relationship each and none of them is with Commmerzbank. Instead, in the previous sample we have 459 firms for this county, with an average Commerzbank dependence of 0.049. Overall, they follow a very similar distribution.

Figure 6: Distribution of the county-level Commerzbank dependence with equal weights (simple county-level average), compared to the measure in the previous version of the draft.

Exposure using Firm-Level Weights

In the following histograms, we compare the distribution of the county-level Commerzbank dependence using the individual level weights for different closing dates, from 2006 to 2008. Using the individual weights for the current ratio, the distribution is quite different along those years due to survivorship bias. Probably the most reliable source is 2007, following Berg et al. (2021). Comparing this distribution with the equally weighted distribution, the former is quite smoother than the latter but with higher distributional mean.

Figure 7: Distribution of the county-level Commerzbank dependence obtained using weights from the ranking of current ratio within the county, using firm-level financial data from 2006, 2007 or 2008.

Figure 8: Distribution of the county-level Commerzbank dependence obtained using weights from the ranking of current ratio from 2007 firm-level financial data within the county, compared to the equal weights.

We repeat the same exercise using individual weights constructed with number of employees and the cost of employees, and we see a very skewed distribution of the county-level Commerzbank dependence towards zero but with higher mean values. Very few firms have complete information about employees. Combining those weights with the weights using the ranking of current ratio does not change the shape of distribution by construction. Using the past data at firm level combined with the available firm level financials used for weighting does not change the distribution significantly.

Figure 9: Distribution of the county-level Commerzbank dependence obtained using weights from the number of employees within the county, using firm-level financial data from 2006, 2007 or 2008.

Figure 10: Distribution of the county-level Commerzbank dependence obtained using weights from the cost of employees within the county, using firm-level financial data from 2006, 2007 or 2008.

Exposure using weights from average values at firm size groups

In this section, we analyse the distribution of the county-level Commerzbank exposure obtained by weighting at firm-level within a county assigning the average value of each financial variable we are considering by firm size category at each firm of that firm size. Just to remember, the mean is taken by using the financials from 2004 to 2008 of those firms that are in the sample. Considering the weights for number or cost of employees against the equally weighted exposure, we obtain a smoother distribution and higher in mean, but we drop a few counties where we do not have financials to take the average for each type of firm size in the available data.

Figure 11: Distribution of the county-level Commerzbank dependence obtained using weights obtained from the average value at firm size level within a county of the total number of employees and the cost of employees, compared to the distribution using equal weights.

When combining the “size” weights obtained from number and cost of employees at firm size level with the inverse current ratio obtained as mean current liabilities over mean current assets by firm size within a county, we still have a smoother distribution than when using equal weights, but it skews the distribution compared to simply using the average cost of employees by firm size for weighting.

Figure 12: Distribution of the county-level Commerzbank dependence obtained using weights obtained from the average value at firm size level within a county of the total number of employees and the cost of employees combined with the current ratio created from mean total assets and mean total liabilities by firm size level within a county, compared to the distribution using equal weights.

The following graphs give an overview of the distribution of county-level Commerzbank dependence for the potentially most relevant and useful weighting techniques among those we have been trying to exploit.

Figure 13: Distribution of county-level Commerzbank dependence, measured using equal weights (first row), weights using the mean value of firm-level data of each firm size category pooling balance sheet data from 2004 to 2008 (second row), and combined weights with the inverse current ratio obtained as the average of current liabilities over the average current assets of each firm size category. The sample of firms is the newly obtained from Amadeus, except for the top right histogram, where the measure is computed using the firm sample from the previous draft.

Spatial Distribution of Commerzbank Dependence

We acquire the boundaries of the administrative areas (1:250 000) from the Federal Agency for Cartography and Geodesy of Germany (VG250). The dataset includes the administrative units of the hierarchical administrative levels from the country (state) down to the Gemeinden (municipalities) with administrative boundaries, key-numbers, names as well as designations. Lines are SingleLines in sf, whereas areas are MultiPolygons. Each area can comprise several single areas, such as regular area with exclaves or inset areas, each of these multipolygons corresponding to a dataset in the attribute table. The data are structured according to levels (country/state), Länder (federal states), Regierungsbezirke (administrative districts), Kreise (districts/counties), Verwaltungsgemeinschaften (administrative associations), Gemeinden (municipalities), whereby the areas contained are directly carrying the attributive information. Spatial reference is UTM projection in zone 32 or 33 Ellipsoid GRS80, Datum ETRS89 (EPSG:4258).

The dataset is divided into the different administrative levels of Germany:

• Staat (country) STA
• Länder (states) LAN
• Administrative districts RBZ
• Districts KRS (what we use)
• Administrative associations VWG
• Municipalities GEM

Also, in the dataset are comprised:

• Boundary lines LI, in each case the respective highest level is included.
• Points PK, each municipality has a point, representing the heart of the municipality.

Data are updated each year with the statuses in 31.12 and 01.01, and we are using the last updated version in September 2021. There are no major changes in the boundaries from 2016. Amtliche Gemeindeschlüssel (AGS) (Official Municipality Key) is the name of what they call KKZ in the SOEP and also present in the Kreise codes matching table, which is derived from the ARS through omission of the administrative association. The hierarchical structure of the administrative levels is represented by the Amtliche Regionalschlüssel (ARS) (territorial code). ARS and AGS constitute the keys of the products of the statistical offices of the Federal Government and of the Länder. Thus, the integration of statistical data and data synchronization, respectively, can easily be performed (cf. also DeStatis).

Details on the attributes:

• ADE administrative level, we want 4 = District
• GF geofactor, which can be 1 (waters without structures), 2 (waters with structures), 3 (land without structures), 4 (land with structure). The indication “waters” refers to the North and Baltic Seas as well as to Lake Constance. Each administrative unit has precisely one record entry with the GF value 4.
• ARS territorial code: 1st-2nd digit for the Land, 3rd digit for the administrative district, 4th-5th digit for the district, 6th-9th digit administrative association, 10th-12th community identification number.
• AGS official municipality key: 1st-2nd digit is for the Land, 3rd digit for the administrative district, 4th-5th digit for the identification number of the district, and 6th-8th digit for the community identification number.
• GEN Geographical Name
• BEZ is the attribute for the district, GEN gives the name, and NBD says whether the attribute should be used for the full name.

We could have actually used this dataset for the identification of firms, but the other table provides us directly with the KKZ codes and not the AGS, which makes it more reliable. I still don’t understand why they are using different names for basically the same codes. For plotting the districts and assign the county values, it is possible to subtract from the shapefile VGS250_KRS everything that is not GF == 4, which is the land with structure, destring the AGS codes and create a new variable kkz_rek from that (they are 401).

Reading layer VG250_KRS' from data source/Users/dubidub/Documents/Projects/CreditPopulism/data/shapes/vg250/VG250_KRS.shp’ using driver `ESRI Shapefile’ Simple feature collection with 431 features and 23 fields Geometry type: MULTIPOLYGON Dimension: XY Bounding box: xmin: 280371.1 ymin: 5235856 xmax: 921292.4 ymax: 6101487 Projected CRS: ETRS89 / UTM zone 32N

Here we plot the spatial distribution of the equally weighted county-level Commerzbank dependence using the current and the past firm sample and the same scale. We also plot the difference in the spatial distribution of the current sample exposure against the past sample. The most striking difference comes from the county that has zero dependence in the current sample, and probably the county for which we have no firms in the current sample. Overall, the current sample calculates smaller values of Commerzbank dependence except for some counties in the south of Germany.

Figure 14: Spatial Distribution of county-level Commerzbank dependence, measured using equal weights, from the current firm sample (left) and the past firm sample (right). The district highlighted in red has zero Commerzbank dependence in the current firm sample. The bins are deciles from the joint distribution of Commerzbank dependence.

Figure 15: Difference in Spatial Distribution of county-level Commerzbank dependence measured using equal weights, current firm sample against past firm sample.

We plot the spatial distribution of the county-level Commerzbank dependence calculated by weighting for the average number of employees and the average cost of employees by firm size category within a county. The re-weighting compared to the equal wights should re-balance the measure for firm heterogeneity in size at county level. The variation from equal weights is stronger for areas where bigger firms are more present, for example Ruhr Valley. The variance of the county exposure measure is much higher compared to equally weighting.

Figure 16: Spatial Distribution of county-level Commerzbank dependence measured using weights obtained from the average number of employees by firm size within a county, compared against equal weights.

Figure 17: Spatial Distribution of county-level Commerzbank dependence measured using weights obtained from the average cost of employees by firm size within a county, compared against equal weights.

We plot the spatial distribution of the county-level Commerzbank dependence calculated by weighting for the average number of employees and the average cost of employees by firm size category within a county. The re-weighting compared to the equal wights should re-balance the measure for firm heterogeneity in size at county level. The variation from equal weights is stronger for areas where bigger firms are more present, for example Ruhr Valley. The variance of the county exposure measure is much higher compared to equally weighting.

To conclude, we plot the spatial distribution of the county-level Commerzbank dependence calculated by combining the weighted average of firm-level Commerzbank dependence within a county with weights for the average number of employees (or average cost of employees) by firm size category within the county and the weighted average of firm-level Commerzbank dependence with weights for the inverse current ratio calculated from the average current assets and the average current liabilities by firm size category within the county. The re-weighting procedure should re-balance the importance of a firm within a county for its size and its exposure to a general liquidity shock in the business cycle. Final values are more balanced.

Figure 18: Spatial Distribution of county-level Commerzbank dependence measured using weights obtained from the average number of employees combined with weights obtained from the inverse current ratio from average current assets and average current liabilities by firm size within a county, compared against equal weights.

Figure 19: Spatial Distribution of county-level Commerzbank dependence measured using weights obtained from the average cost of employees combined with weights obtained from the inverse current ratio from average current assets and average current liabilities by firm size within a county, compared against equal weights.